import akshare as ak
import pandas as pd
import pymysql
import logging

# create mysql connect
connect = pymysql.connect(host='192.168.0.2', port=3306, user='root', passwd='', database='stock')
cursor = connect.cursor()

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.float_format', '{:,}'.format)
pd.set_option('expand_frame_repr', False)

stock_list = ak.stock_zh_a_spot_em()
size = stock_list.shape[0]

for i in range(size):

    try:
        stock_code = stock_list.loc[i, "代码"]

        logging.info("read stock {} start".format(stock_code))

        stock_his_data = ak.stock_zh_a_hist(symbol=stock_code, period="daily", start_date="20200101",
                                            adjust="qfq", timeout=50000)

        stock_his_data = stock_his_data.sort_values("日期")

        stock_data = pd.DataFrame()
        stock_data["date"] = stock_his_data["日期"]
        stock_data["code"] = stock_his_data["股票代码"]
        stock_data["open"] = stock_his_data["开盘"]
        stock_data["close"] = stock_his_data["收盘"]
        stock_data["high"] = stock_his_data["最高"]
        stock_data["low"] = stock_his_data["最低"]
        stock_data["vol"] = stock_his_data["成交量"]
        stock_data["vol_amount"] = stock_his_data["成交额"]
        stock_data["change"] = stock_his_data["换手率"]
        stock_data["increment"] = stock_his_data["涨跌幅"]

        stock_data_new = pd.DataFrame(stock_data)

        stock_data_new["zz"] = (stock_data_new["open"] + stock_data_new["low"] + stock_data_new["high"]) / 3
        stock_data_new["zl1"] = stock_data_new["zz"].ewm(span=7, adjust=True).mean().round(2)
        stock_data_new["zl2"] = stock_data_new["zz"].ewm(span=55, adjust=True).mean().round(2)
        stock_data_new = stock_data_new.drop('zz', axis=1)
        stock_data_new['dif'] = stock_data_new["close"].ewm(span=12, adjust=False).mean() - stock_data_new["close"].ewm(
            span=26,
            adjust=False).mean()
        stock_data_new['c20'] = stock_data_new["close"].ewm(span=7, adjust=False).mean()
        # params
        p1 = 0.20
        p2 = 0.03
        n = 4
        p = 3

        for index in range(0, len(stock_data_new)):
            ## c1 condition (zl1 > zl2)
            if stock_data_new.loc[index, "zl1"] > stock_data_new.loc[index, "zl2"]:
                stock_data_new.loc[index, "c1"] = 1
            else:
                stock_data_new.loc[index, "c1"] = 0
            ## c2 condition (c < zl2)
            if (stock_data_new.loc[index, "close"] < stock_data_new.loc[index, "zl1"]):
                stock_data_new.loc[index, "c2_1"] = 1
            else:
                stock_data_new.loc[index, "c2_1"] = 0

            # if stock_data_new.loc[index, "date"].strftime("%Y-%m-%d") == '2025-03-06':
            #     print(stock_data_new.loc[index - n: index])
            #     print("-----------------------------------")
            #     print(stock_data_new.loc[index - n: index]["c2_1"] > 0)
            #     print("-----------------------------------")
            #     print((stock_data_new.loc[index - n: index][stock_data_new.loc[index - n: index]["c2_1"] > 0]).shape[0])

            if index > n and \
                    (stock_data_new.loc[index - n: index - p][
                        stock_data_new.loc[index - n: index - p]["c2_1"] > 0]).shape[
                        0] == n - p + 1:
                stock_data_new.loc[index, "c2"] = 1
            else:
                stock_data_new.loc[index, "c2"] = 0

            ## c3 condition (c > zl1)
            if stock_data_new.loc[index, "close"] > stock_data_new.loc[index, "zl1"]:
                stock_data_new.loc[index, "c3_2"] = 1
            else:
                stock_data_new.loc[index, "c3_2"] = 0

            if index > p and \
                    (
                            stock_data_new.loc[index - p + 1:index][
                                stock_data_new.loc[index - p + 1:index]["c3_2"] > 0]).shape[
                        0] == p and stock_data_new.loc[index - p, "open"] < stock_data_new.loc[index, "zl1"]:
                stock_data_new.loc[index, "c3"] = 1
            else:
                stock_data_new.loc[index, "c3"] = 0

            ## c4 condition (v/ref(v,1) <=0.20)
            if index >= 1 and stock_data_new.loc[index - 1, "close"] is not None:
                if abs(stock_data_new.loc[index, "vol"] - stock_data_new.loc[index - 1, "vol"]) / stock_data_new.loc[
                    index - 1, "vol"] < p1:
                    stock_data_new.loc[index, "c4"] = 1
                else:
                    stock_data_new.loc[index, "c4"] = 0
            else:
                stock_data_new.loc[index, "c4"] = 0

            ## c5
            if index >= 1 and stock_data_new.loc[index - 1, "dif"] is not None:
                if stock_data_new.loc[index, "dif"] >= stock_data_new.loc[index - 1, "dif"]:
                    stock_data_new.loc[index, "c5"] = 1
                else:
                    stock_data_new.loc[index, "c5"] = 0
            else:
                stock_data_new.loc[index, "c5"] = 0

            ## c6
            if stock_data_new.loc[index, "close"] > stock_data_new.loc[index, "zl1"] and stock_data_new.loc[
                index, "close"] > \
                    stock_data_new.loc[index, "open"]:
                stock_data_new.loc[index, "c6"] = 1
            else:
                stock_data_new.loc[index, "c6"] = 0

            # c7
            if index >= 1 and (stock_data_new.loc[index, "high"] - stock_data_new.loc[index, "close"]) / \
                    stock_data_new.loc[
                        index - 1, "close"] <= p2:
                stock_data_new.loc[index, "c7"] = 1
            else:
                stock_data_new.loc[index, "c7"] = 0

            # c8
            if index >= 2 and stock_data_new.loc[index, "close"] > stock_data_new.loc[index - 1, "close"] and \
                    stock_data_new.loc[
                        index, "close"] > stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_1"] = 1
            else:
                stock_data_new.loc[index, "c8_1"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_1"] == 1 and stock_data_new.loc[
                index - 1, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_2"] = 1
            else:
                stock_data_new.loc[index, "c8_2"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_2"] == 1 and stock_data_new.loc[
                index - 2, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c8_3"] = 1
            else:
                stock_data_new.loc[index, "c8_3"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_3"] == 1 and stock_data_new.loc[
                index - 1, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_4"] = 1
            else:
                stock_data_new.loc[index, "c8_4"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_4"] == 1 and stock_data_new.loc[
                index - 2, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c8_5"] = 1
            else:
                stock_data_new.loc[index, "c8_5"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_5"] == 1 and stock_data_new.loc[
                index - 1, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_6"] = 1
            else:
                stock_data_new.loc[index, "c8_6"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_6"] == 1 and stock_data_new.loc[
                index - 2, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c8_7"] = 1
            else:
                stock_data_new.loc[index, "c8_7"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_7"] == 1 and stock_data_new.loc[
                index - 1, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_8"] = 1
            else:
                stock_data_new.loc[index, "c8_8"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_8"] == 1 and stock_data_new.loc[
                index - 2, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c8_9"] = 1
            else:
                stock_data_new.loc[index, "c8_9"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_9"] == 1 and stock_data_new.loc[
                index - 1, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_10"] = 1
            else:
                stock_data_new.loc[index, "c8_10"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_10"] == 1 and stock_data_new.loc[
                index - 2, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c8_11"] = 1
            else:
                stock_data_new.loc[index, "c8_11"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c8_11"] == 1 and stock_data_new.loc[
                index - 1, "close"] >= stock_data_new.loc[index, "close"] >= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c8_12"] = 1
            else:
                stock_data_new.loc[index, "c8_12"] = 0

            # c9
            if index >= 2 and stock_data_new.loc[index, "close"] < stock_data_new.loc[index - 1, "close"] and \
                    stock_data_new.loc[
                        index, "close"] < stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_1"] = 1
            else:
                stock_data_new.loc[index, "c9_1"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_1"] == 1 and stock_data_new.loc[index - 1, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_2"] = 1
            else:
                stock_data_new.loc[index, "c9_2"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_2"] == 1 and stock_data_new.loc[index - 2, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c9_3"] = 1
            else:
                stock_data_new.loc[index, "c9_3"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_3"] == 1 and stock_data_new.loc[index - 1, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_4"] = 1
            else:
                stock_data_new.loc[index, "c9_4"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_4"] == 1 and stock_data_new.loc[index - 2, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c9_5"] = 1
            else:
                stock_data_new.loc[index, "c9_5"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_5"] == 1 and stock_data_new.loc[index - 1, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_6"] = 1
            else:
                stock_data_new.loc[index, "c9_6"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_6"] == 1 and stock_data_new.loc[index - 2, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c9_7"] = 1
            else:
                stock_data_new.loc[index, "c9_7"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_7"] == 1 and stock_data_new.loc[index - 1, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_8"] = 1
            else:
                stock_data_new.loc[index, "c9_8"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_8"] == 1 and stock_data_new.loc[index - 2, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c9_9"] = 1
            else:
                stock_data_new.loc[index, "c9_9"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_9"] == 1 and stock_data_new.loc[index - 1, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_10"] = 1
            else:
                stock_data_new.loc[index, "c9_10"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_10"] == 1 and stock_data_new.loc[index - 2, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 1, "close"]:
                stock_data_new.loc[index, "c9_11"] = 1
            else:
                stock_data_new.loc[index, "c9_11"] = 0

            if index >= 2 and stock_data_new.loc[index - 1, "c9_11"] == 1 and stock_data_new.loc[index - 1, "close"] <= \
                    stock_data_new.loc[
                        index, "close"] <= stock_data_new.loc[index - 2, "close"]:
                stock_data_new.loc[index, "c9_12"] = 1
            else:
                stock_data_new.loc[index, "c9_12"] = 0

            # c8 buy
            if index >= 1 and (stock_data_new.loc[index - 1, "c9_1"] == 1 or
                               stock_data_new.loc[index - 1, "c9_2"] == 1 or
                               stock_data_new.loc[index - 1, "c9_3"] == 1 or
                               stock_data_new.loc[index - 1, "c9_4"] == 1 or
                               stock_data_new.loc[index - 1, "c9_5"] == 1 or
                               stock_data_new.loc[index - 1, "c9_6"] == 1 or
                               stock_data_new.loc[index - 1, "c9_7"] == 1 or
                               stock_data_new.loc[index - 1, "c9_8"] == 1 or
                               stock_data_new.loc[index - 1, "c9_9"] == 1 or
                               stock_data_new.loc[index - 1, "c9_10"] == 1 or
                               stock_data_new.loc[index - 1, "c9_11"] == 1 or
                               stock_data_new.loc[index - 1, "c9_12"] == 1) and stock_data_new.loc[index, "c8_1"] == 1:
                stock_data_new.loc[index, "c8"] = 1
            else:
                stock_data_new.loc[index, "c8"] = 0

            # c9 sell
            if (index >= 1 and (
                    stock_data_new.loc[index - 1, "c8_1"] == 1 or stock_data_new.loc[index - 1, "c8_2"] == 1 or
                    stock_data_new.loc[index - 1, "c8_3"] == 1 or stock_data_new.loc[index - 1, "c8_4"] == 1 or
                    stock_data_new.loc[index - 1, "c8_5"] == 1 or stock_data_new.loc[index - 1, "c8_6"] == 1 or
                    stock_data_new.loc[index - 1, "c8_7"] == 1 or stock_data_new.loc[index - 1, "c8_8"] == 1 or
                    stock_data_new.loc[index - 1, "c8_9"] == 1 or stock_data_new.loc[index - 1, "c8_10"] == 1 or
                    stock_data_new.loc[index - 1, "c8_11"] == 1 or stock_data_new.loc[index - 1, "c8_12"] == 1)
                    and stock_data_new.loc[index, "c9_1"] == 1):
                stock_data_new.loc[index, "c9"] = 1
            else:
                stock_data_new.loc[index, "c9"] = 0

            # c10(红箭头)
            if index >= 2 and stock_data_new.loc[index, "close"] > stock_data_new.loc[index, "zl1"] and \
                    stock_data_new.loc[
                        index, "c8"] > 0:
                stock_data_new.loc[index, "c10"] = 1
            else:
                stock_data_new.loc[index, "c10"] = 0

            if index > n and \
                    (stock_data_new.loc[index - p + 1:index][stock_data_new.loc[index - p + 1:index]["c10"] > 0]).shape[
                        0] >= 1:
                stock_data_new.loc[index, "c11"] = 1
            else:
                stock_data_new.loc[index, "c11"] = 0

            # condition constructor
            if (stock_data_new.loc[index, "c1"] > 0 and stock_data_new.loc[index, "c2"] > 0 and
                    stock_data_new.loc[index, "c3"] > 0 and stock_data_new.loc[index, "c4"] > 0 and stock_data_new.loc[
                        index, "c5"] > 0 and \
                    stock_data_new.loc[index, "c6"] > 0 and stock_data_new.loc[index, "c7"] > 0 and stock_data_new.loc[
                        index, "c11"] > 0):
                stock_data_new.loc[index, "flag"] = 1
            else:
                stock_data_new.loc[index, "flag"] = 0

        # data into mysql
        for idx in range(stock_data_new.shape[0]):
            code = stock_data_new.loc[idx, "code"]
            date = stock_data_new.loc[idx, "date"]
            open = stock_data_new.loc[idx, "open"]
            close = stock_data_new.loc[idx, "close"]
            high = stock_data_new.loc[idx, "high"]
            low = stock_data_new.loc[idx, "low"]
            vol = stock_data_new.loc[idx, "vol"]
            vol_amount = stock_data_new.loc[idx, "vol_amount"]
            change = stock_data_new.loc[idx, "change"]
            increment = stock_data_new.loc[idx, "increment"]
            flag = stock_data_new.loc[idx, "flag"]

            cursor.execute("insert into stock_date values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')" % (
                code, date, open, close, high, low, vol, vol_amount, change, increment, flag))

            connect.commit()

        logging.info("read stock {} finished".format(stock_code))

    except Exception as e:
        print(e)
